﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ClassLibrary
{
    public class connectionClass
    {
        SqlConnection conn = new SqlConnection();
        private SqlCommand cmd;
        private SqlDataAdapter da = new SqlDataAdapter();
        private DataSet ds = new DataSet();
        private SqlTransaction tr;
        private SqlCommandBuilder cb = new SqlCommandBuilder();

        public void getConnection(){
       
            try{
                conn.ConnectionString = @"Data Source=EUSEBIUS-N76\SQLEXPRESS;Initial Catalog=groep16_festivals;Integrated Security=True";
            }
            catch (Exception ex){
                Logs.Log(ex.Message + " " + ex.StackTrace);
            }
        }

        public void getConnectionAdmin()
        {
            try{
                conn.ConnectionString = @"Data Source=(LocalDb)\v11.0;AttachDbFilename=D:\Users\Eusebius\Documents\School\DotNet\Periode 2\Oefeningen\WebApplication2\WebApplication2\App_Data\groep16_festivals.mdf;Initial Catalog=groep16_festivals;Integrated Security=True";
            }
            catch (Exception ex){
                Logs.Log(ex.Message + " " + ex.StackTrace);
            }
        }

        public void closeConnection()
        {
            try
            {

                if (conn.State == ConnectionState.Open)
                    conn.Close();
            }
            catch (Exception ex)
            {
                Logs.Log(ex.Message + ex.StackTrace);
            }
        }

        public DataSet getFestivals()
        {
            try
            {
                cmd = new SqlCommand();
                cmd.CommandType = CommandType.Text;
                cmd.Connection = conn;
                cmd.CommandText = "SELECT * FROM festivals";

                cb.DataAdapter = da;
                da.SelectCommand = cmd;
                da.Fill(ds, "festivalTable");
            }
            catch (Exception ex)
            {
                Logs.Log(ex.Message + " " + ex.StackTrace);
                da = null;
            }
            return ds;           
        }

        public DataSet getBandNames()
        {
            try
            {
                cmd = new SqlCommand();
                cmd.CommandType = CommandType.Text;
                cmd.Connection = conn;
                cmd.CommandText = "SELECT band_naam FROM bands";

                cb.DataAdapter = da;
                da.SelectCommand = cmd;
                da.Fill(ds, "bandNames");
            }
            catch (Exception ex)
            {
                Logs.Log(ex.Message + " " + ex.StackTrace);
                da = null;
            }
            return ds;       
        }

        public DataSet getDataFromTable(String table){
            try
            {
                cmd = new SqlCommand();
                cmd.CommandType = CommandType.Text;
                cmd.Connection = conn;
                cmd.CommandText = "SELECT * FROM " + table;

                cb.DataAdapter = da;
                da.SelectCommand = cmd;
                da.Fill(ds, "tableData");
            }
            catch (Exception ex)
            {
                Logs.Log(ex.Message + " " + ex.StackTrace);
                da = null;
            }
            return ds;           
        }

        public DataSet getTableNames()
        {
            try{
                cmd = new SqlCommand();
                cmd.CommandType = CommandType.Text;
                cmd.Connection = conn;
                cmd.CommandText = "SELECT * FROM sysobjects where xtype='U'";
                cb.DataAdapter = da;
                da.SelectCommand = cmd;
                da.Fill(ds, "namesData");
            }
            catch (Exception ex)
            {
                Logs.Log(ex.Message + " " + ex.StackTrace);
                da = null;
            }
            return ds;           
        }

        public bool updateBands(int Bandid, String bandName, String genre, String website)
        {
            bool success = false;
            try
            {
                if (conn.State == ConnectionState.Open)
                {
                    tr = conn.BeginTransaction();
                    cmd = new SqlCommand();
                    cmd.Transaction = tr;                    
                    cmd.CommandType = CommandType.Text;
                    cmd.Connection = conn;
                    cmd.CommandText = "UPDATE bands SET band_naam='" + bandName + "', band_soortMuziek='" + genre + "', band_url='" + website + "' WHERE band_id='" + Bandid + "'";

                    cmd.ExecuteNonQuery();
                    tr.Commit();
                    success = true;                    
                }
                else
                {
                    conn.Open();
                    tr = conn.BeginTransaction();
                    cmd = new SqlCommand();
                    cmd.Transaction = tr;
                    cmd.CommandType = CommandType.Text;
                    cmd.Connection = conn;
                    cmd.CommandText = "UPDATE bands SET band_naam='" + bandName + "', band_soortMuziek='" + genre + "', band_url='" + website + "' WHERE band_id='" + Bandid + "'";

                    cmd.ExecuteNonQuery();
                    tr.Commit();
                    success = true;
                }
                return success;
            }
            catch (Exception ex)
            {
                Logs.Log(ex.Message + " " + ex.StackTrace);
                return false;
            }
        }

        public bool deleteBands(int id)
        {
             bool success = false;
             try
             {
                 if (conn.State == ConnectionState.Open)
                 {
                     tr = conn.BeginTransaction();
                     cmd = new SqlCommand();
                     cmd.Transaction = tr;
                     cmd.CommandType = CommandType.Text;
                     cmd.Connection = conn;
                     cmd.CommandText = "DELETE FROM bands WHERE band_id='" + id + "'";

                     cmd.ExecuteNonQuery();
                     tr.Commit();
                     success = true;         
                 }
                 else
                 {
                     conn.Open();
                     tr = conn.BeginTransaction();
                     cmd = new SqlCommand();
                     cmd.Transaction = tr;
                     cmd.CommandType = CommandType.Text;
                     cmd.Connection = conn;
                     cmd.CommandText = "DELETE FROM bands WHERE band_id='" + id + "'";

                     cmd.ExecuteNonQuery();
                     tr.Commit();
                     success = true;   
                 }
                 return success;
             }
             catch (Exception ex)
             {
                 Logs.Log(ex.Message + " " + ex.StackTrace);
                 return false;
             }
        }

        public bool updateBandsperfestival(int festId, int bandId, int podId, DateTime date, DateTime hour)
        {
            bool success = false;
            try
            {
                if (conn.State == ConnectionState.Open)
                {
                    tr = conn.BeginTransaction();
                    cmd = new SqlCommand();
                    cmd.Transaction = tr;
                    cmd.CommandType = CommandType.Text;
                    cmd.Connection = conn;
                    cmd.CommandText = "UPDATE bandsperfestival SET pod_id='" + podId + "', uur='" + hour + "' WHERE fest_id='" + festId + "' AND band_id='" + bandId + "' AND datum ='" + date + "'";

                    cmd.ExecuteNonQuery();
                    tr.Commit();
                    success = true;
                }
                else
                {
                    conn.Open();
                    tr = conn.BeginTransaction();
                    cmd = new SqlCommand();
                    cmd.Transaction = tr;
                    cmd.CommandType = CommandType.Text;
                    cmd.Connection = conn;
                    cmd.CommandText = "UPDATE bandsperfestival SET pod_id='" + podId + "', uur='" + hour + "' WHERE fest_id='" + festId + "' AND band_id='" + bandId + "' AND datum ='" + date + "'";

                    cmd.ExecuteNonQuery();
                    tr.Commit();
                    success = true;
                }
                return success;
            }
            catch (Exception ex)
            {
                Logs.Log(ex.Message + " " + ex.StackTrace);
                return false;
            }
        }

        public bool deleteBandsperfestival(int festId, int bandId, DateTime date)
        {
            bool success = false;
            try
            {
                if (conn.State == ConnectionState.Open)
                {
                    tr = conn.BeginTransaction();
                    cmd = new SqlCommand();
                    cmd.Transaction = tr;
                    cmd.CommandType = CommandType.Text;
                    cmd.Connection = conn;
                    cmd.CommandText = "DELETE FROM bandsperfestival WHERE fest_id='" + festId + "' AND band_id='" + bandId + "' AND datum='" + date + "'";

                    cmd.ExecuteNonQuery();
                    tr.Commit();
                    success = true;
                }
                else
                {
                    conn.Open();
                    tr = conn.BeginTransaction();
                    cmd = new SqlCommand();
                    cmd.Transaction = tr;
                    cmd.CommandType = CommandType.Text;
                    cmd.Connection = conn;
                    cmd.CommandText = "DELETE FROM bandsperfestival WHERE fest_id='" + festId + "' AND band_id='" + bandId + "' AND datum='" + date + "'";

                    cmd.ExecuteNonQuery();
                    tr.Commit();
                    success = true;
                }
                return success;
            }
            catch (Exception ex)
            {
                Logs.Log(ex.Message + " " + ex.StackTrace);
                return false;
            }
        }

        public bool updateCampings(int campId, String campAddress, int campCap)
        {
            bool success = false;
            try
            {
                if (conn.State == ConnectionState.Open)
                {
                    tr = conn.BeginTransaction();
                    cmd = new SqlCommand();
                    cmd.Transaction = tr;
                    cmd.CommandType = CommandType.Text;
                    cmd.Connection = conn;
                    cmd.CommandText = "UPDATE campings SET camp_adres='" + campAddress + "', camp_cap='" + campCap + "' WHERE camp_id='" + campId + "'";

                    cmd.ExecuteNonQuery();
                    tr.Commit();
                    success = true;
                }
                else
                {
                    conn.Open();
                    tr = conn.BeginTransaction();
                    cmd = new SqlCommand();
                    cmd.Transaction = tr;
                    cmd.CommandType = CommandType.Text;
                    cmd.Connection = conn;
                    cmd.CommandText = "UPDATE campings SET camp_adres='" + campAddress + "', camp_cap='" + campCap + "' WHERE camp_id='" + campId + "'";

                    cmd.ExecuteNonQuery();
                    tr.Commit();
                    success = true;
                }
                return success;
            }
            catch (Exception ex)
            {
                Logs.Log(ex.Message + " " + ex.StackTrace);
                return false;
            }
        }

        public bool deleteCampings(int campId)
        {
            bool success = false;
            try
            {
                if (conn.State == ConnectionState.Open)
                {
                    tr = conn.BeginTransaction();
                    cmd = new SqlCommand();
                    cmd.Transaction = tr;
                    cmd.CommandType = CommandType.Text;
                    cmd.Connection = conn;
                    cmd.CommandText = "DELETE FROM campings WHERE camp_id='" + campId + "'";

                    cmd.ExecuteNonQuery();
                    tr.Commit();
                    success = true;
                }
                else
                {
                    conn.Open();
                    tr = conn.BeginTransaction();
                    cmd = new SqlCommand();
                    cmd.Transaction = tr;
                    cmd.CommandType = CommandType.Text;
                    cmd.Connection = conn;
                    cmd.CommandText = "DELETE FROM campings WHERE camp_id='" + campId + "'";

                    cmd.ExecuteNonQuery();
                    tr.Commit();
                    success = true;
                }
                return success;
            }
            catch (Exception ex)
            {
                Logs.Log(ex.Message + " " + ex.StackTrace);
                return false;
            }
        }

        public bool updateFaciliteiten(int facId, String facDesc, Decimal facPrice)
        {
            bool success = false;
            try
            {
                if (conn.State == ConnectionState.Open)
                {
                    tr = conn.BeginTransaction();
                    cmd = new SqlCommand();
                    cmd.Transaction = tr;
                    cmd.CommandType = CommandType.Text;
                    cmd.Connection = conn;
                    cmd.CommandText = "UPDATE faciliteiten SET fac_omschr='" + facDesc + "', fac_eenhprijs='" + facPrice + "' WHERE fac_id='" + facId + "'";

                    cmd.ExecuteNonQuery();
                    tr.Commit();
                    success = true;
                }
                else
                {
                    conn.Open();
                    tr = conn.BeginTransaction();
                    cmd = new SqlCommand();
                    cmd.Transaction = tr;
                    cmd.CommandType = CommandType.Text;
                    cmd.Connection = conn;
                    cmd.CommandText = "UPDATE faciliteiten SET fac_omschr='" + facDesc + "', fac_eenhprijs='" + facPrice + "' WHERE fac_id='" + facId + "'";

                    cmd.ExecuteNonQuery();
                    tr.Commit();
                    success = true;
                }
                return success;
            }
            catch (Exception ex)
            {
                Logs.Log(ex.Message + " " + ex.StackTrace);
                return false;
            }
        }

        public bool deleteFaciliteiten(int facId)
        {
            bool success = false;
            try
            {
                if (conn.State == ConnectionState.Open)
                {
                    tr = conn.BeginTransaction();
                    cmd = new SqlCommand();
                    cmd.Transaction = tr;
                    cmd.CommandType = CommandType.Text;
                    cmd.Connection = conn;
                    cmd.CommandText = "DELETE FROM faciliteiten WHERE fac_id='" + facId + "'";

                    cmd.ExecuteNonQuery();
                    tr.Commit();
                    success = true;
                }
                else
                {
                    conn.Open();
                    tr = conn.BeginTransaction();
                    cmd = new SqlCommand();
                    cmd.Transaction = tr;
                    cmd.CommandType = CommandType.Text;
                    cmd.Connection = conn;
                    cmd.CommandText = "DELETE FROM faciliteiten WHERE fac_id='" + facId + "'";

                    cmd.ExecuteNonQuery();
                    tr.Commit();
                    success = true;
                }
                return success;
            }
            catch (Exception ex)
            {
                Logs.Log(ex.Message + " " + ex.StackTrace);
                return false;
            }
        }

        public bool updateFacpercamp(int campId, int facId, int number)
        {
            bool success = false;
            try
            {
                if (conn.State == ConnectionState.Open)
                {
                    tr = conn.BeginTransaction();
                    cmd = new SqlCommand();
                    cmd.Transaction = tr;
                    cmd.CommandType = CommandType.Text;
                    cmd.Connection = conn;
                    cmd.CommandText = "UPDATE facpercamp SET aantal='" + number + "' WHERE fac_id='" + facId + "' AND camp_id ='" + campId + "'";

                    cmd.ExecuteNonQuery();
                    tr.Commit();
                    success = true;
                }
                else
                {
                    conn.Open();
                    tr = conn.BeginTransaction();
                    cmd = new SqlCommand();
                    cmd.Transaction = tr;
                    cmd.CommandType = CommandType.Text;
                    cmd.Connection = conn;
                    cmd.CommandText = "UPDATE facpercamp SET aantal='" + number + "' WHERE fac_id='" + facId + "' AND camp_id ='" + campId + "'";

                    cmd.ExecuteNonQuery();
                    tr.Commit();
                    success = true;
                }
                return success;
            }
            catch (Exception ex)
            {
                Logs.Log(ex.Message + " " + ex.StackTrace);
                return false;
            }
        }

        public bool deleteFacpercamp(int campId, int facId)
        {
            bool success = false;
            try
            {
                if (conn.State == ConnectionState.Open)
                {
                    tr = conn.BeginTransaction();
                    cmd = new SqlCommand();
                    cmd.Transaction = tr;
                    cmd.CommandType = CommandType.Text;
                    cmd.Connection = conn;
                    cmd.CommandText = "DELETE FROM facpercamp WHERE fac_id='" + facId + "' AND camp_id='" + campId + "'";

                    cmd.ExecuteNonQuery();
                    tr.Commit();
                    success = true;
                }
                else
                {
                    conn.Open();
                    tr = conn.BeginTransaction();
                    cmd = new SqlCommand();
                    cmd.Transaction = tr;
                    cmd.CommandType = CommandType.Text;
                    cmd.Connection = conn;
                    cmd.CommandText = "DELETE FROM facpercamp WHERE fac_id='" + facId + "' AND camp_id='" + campId + "'";

                    cmd.ExecuteNonQuery();
                    tr.Commit();
                    success = true;
                }
                return success;
            }
            catch (Exception ex)
            {
                Logs.Log(ex.Message + " " + ex.StackTrace);
                return false;
            }
        }

        public bool updateFestivals(int festId, String festName, String festLocation, DateTime festDate, int festDuration)
        {
            bool success = false;
            try
            {
                if (conn.State == ConnectionState.Open)
                {
                    tr = conn.BeginTransaction();
                    cmd = new SqlCommand();
                    cmd.Transaction = tr;
                    cmd.CommandType = CommandType.Text;
                    cmd.Connection = conn;
                    cmd.CommandText = "UPDATE festivals SET fest_naam='" + festName + "', fest_locatie='" + festLocation + "', fest_datum='" + festDate + "', fest_duur='" + festDuration + "' WHERE fest_id='" + festId + "'";

                    cmd.ExecuteNonQuery();
                    tr.Commit();
                    success = true;
                }
                else
                {
                    conn.Open();
                    tr = conn.BeginTransaction();
                    cmd = new SqlCommand();
                    cmd.Transaction = tr;
                    cmd.CommandType = CommandType.Text;
                    cmd.Connection = conn;
                    cmd.CommandText = "UPDATE festivals SET fest_naam='" + festName + "', fest_locatie='" + festLocation + "', fest_datum='" + festDate + "', fest_duur='" + festDuration + "' WHERE fest_id='" + festId + "'";

                    cmd.ExecuteNonQuery();
                    tr.Commit();
                    success = true;
                }
                return success;
            }
            catch (Exception ex)
            {
                Logs.Log(ex.Message + " " + ex.StackTrace);
                return false;
            }
        }

        public bool deleteFestivals(int festId)
        {
            bool success = false;
            try
            {
                if (conn.State == ConnectionState.Open)
                {
                    tr = conn.BeginTransaction();
                    cmd = new SqlCommand();
                    cmd.Transaction = tr;
                    cmd.CommandType = CommandType.Text;
                    cmd.Connection = conn;
                    cmd.CommandText = "DELETE FROM festivals WHERE fest_id='" + festId + "'";

                    cmd.ExecuteNonQuery();
                    tr.Commit();
                    success = true;
                }
                else
                {
                    conn.Open();
                    tr = conn.BeginTransaction();
                    cmd = new SqlCommand();
                    cmd.Transaction = tr;
                    cmd.CommandType = CommandType.Text;
                    cmd.Connection = conn;
                    cmd.CommandText = "DELETE FROM festivals WHERE fest_id='" + festId + "'";

                    cmd.ExecuteNonQuery();
                    tr.Commit();
                    success = true;
                }
                return success;
            }
            catch (Exception ex)
            {
                Logs.Log(ex.Message + " " + ex.StackTrace);
                return false;
            }
        }

        public bool updateGeregistreerdegebruikers(int userId, String userName, String userAddress, DateTime userBDate, String userPass)
        {
            bool success = false;
            try
            {
                if (conn.State == ConnectionState.Open)
                {
                    tr = conn.BeginTransaction();
                    cmd = new SqlCommand();
                    cmd.Transaction = tr;
                    cmd.CommandType = CommandType.Text;
                    cmd.Connection = conn;
                    cmd.CommandText = "UPDATE geregistreerdegebruikers SET gebr_naam='" + userName + "', gebr_adres='" + userAddress + "', gebr_gebDat='" + userBDate + "', gebr_pass='" + userPass + "' WHERE gebr_id='" + userId + "'";

                    cmd.ExecuteNonQuery();
                    tr.Commit();
                    success = true;
                }
                else
                {
                    conn.Open();
                    tr = conn.BeginTransaction();
                    cmd = new SqlCommand();
                    cmd.Transaction = tr;
                    cmd.CommandType = CommandType.Text;
                    cmd.Connection = conn;
                    cmd.CommandText = "UPDATE geregistreerdegebruikers SET gebr_naam='" + userName + "', gebr_adres='" + userAddress + "', gebr_gebDat='" + userBDate + "', gebr_pass='" + userPass + "' WHERE gebr_id='" + userId + "'";

                    cmd.ExecuteNonQuery();
                    tr.Commit();
                    success = true;
                }
                return success;
            }
            catch (Exception ex)
            {
                Logs.Log(ex.Message + " " + ex.StackTrace);
                return false;
            }
        }

        public bool deleteGeregistreerdegebruikers(int userId)
        {
            bool success = false;
            try
            {
                if (conn.State == ConnectionState.Open)
                {
                    tr = conn.BeginTransaction();
                    cmd = new SqlCommand();
                    cmd.Transaction = tr;
                    cmd.CommandType = CommandType.Text;
                    cmd.Connection = conn;
                    cmd.CommandText = "DELETE FROM geregistreerdegebruikers WHERE gebr_id='" + userId + "'";

                    cmd.ExecuteNonQuery();
                    tr.Commit();
                    success = true;
                }
                else
                {
                    conn.Open();
                    tr = conn.BeginTransaction();
                    cmd = new SqlCommand();
                    cmd.Transaction = tr;
                    cmd.CommandType = CommandType.Text;
                    cmd.Connection = conn;
                    cmd.CommandText = "DELETE FROM geregistreerdegebruikers WHERE gebr_id='" + userId + "'";

                    cmd.ExecuteNonQuery();
                    tr.Commit();
                    success = true;
                }
                return success;
            }
            catch (Exception ex)
            {
                Logs.Log(ex.Message + " " + ex.StackTrace);
                return false;
            }
        }

        public bool updatePodia(int podId, String podDesc, String podLocation)
        {
            bool success = false;
            try
            {
                if (conn.State == ConnectionState.Open)
                {
                    tr = conn.BeginTransaction();
                    cmd = new SqlCommand();
                    cmd.Transaction = tr;
                    cmd.CommandType = CommandType.Text;
                    cmd.Connection = conn;
                    cmd.CommandText = "UPDATE podia SET pod_omschr='" + podDesc + "', pod_locatie='" + podLocation + "' WHERE pod_id='" + podId + "'";

                    cmd.ExecuteNonQuery();
                    tr.Commit();
                    success = true;
                }
                else
                {
                    conn.Open();
                    tr = conn.BeginTransaction();
                    cmd = new SqlCommand();
                    cmd.Transaction = tr;
                    cmd.CommandType = CommandType.Text;
                    cmd.Connection = conn;
                    cmd.CommandText = "UPDATE podia SET pod_omschr='" + podDesc + "', pod_locatie='" + podLocation + "' WHERE pod_id='" + podId + "'";

                    cmd.ExecuteNonQuery();
                    tr.Commit();
                    success = true;
                }
                return success;
            }
            catch (Exception ex)
            {
                Logs.Log(ex.Message + " " + ex.StackTrace);
                return false;
            }
        }

        public bool deletePodia(int podId)
        {
            bool success = false;
            try
            {
                if (conn.State == ConnectionState.Open)
                {
                    tr = conn.BeginTransaction();
                    cmd = new SqlCommand();
                    cmd.Transaction = tr;
                    cmd.CommandType = CommandType.Text;
                    cmd.Connection = conn;
                    cmd.CommandText = "DELETE FROM podia WHERE pod_id='" + podId + "'";

                    cmd.ExecuteNonQuery();
                    tr.Commit();
                    success = true;
                }
                else
                {
                    conn.Open();
                    tr = conn.BeginTransaction();
                    cmd = new SqlCommand();
                    cmd.Transaction = tr;
                    cmd.CommandType = CommandType.Text;
                    cmd.Connection = conn;
                    cmd.CommandText = "DELETE FROM podia WHERE pod_id='" + podId + "'";

                    cmd.ExecuteNonQuery();
                    tr.Commit();
                    success = true;
                }
                return success;
            }
            catch (Exception ex)
            {
                Logs.Log(ex.Message + " " + ex.StackTrace);
                return false;
            }
        }

        public bool updateTickets(int festId, int userId, int typId, DateTime ticketDate)
        {
            bool success = false;
            try
            {
                if (conn.State == ConnectionState.Open)
                {
                    tr = conn.BeginTransaction();
                    cmd = new SqlCommand();
                    cmd.Transaction = tr;
                    cmd.CommandType = CommandType.Text;
                    cmd.Connection = conn;
                    cmd.CommandText = "UPDATE tickets SET typ_id='" + typId + "', ticket_datum = '" + ticketDate + "' WHERE fest_id='" + festId + "' AND gebr_id ='" + userId + "'";

                    cmd.ExecuteNonQuery();
                    tr.Commit();
                    success = true;
                }
                else
                {
                    conn.Open();
                    tr = conn.BeginTransaction();
                    cmd = new SqlCommand();
                    cmd.Transaction = tr;
                    cmd.CommandType = CommandType.Text;
                    cmd.Connection = conn;
                    cmd.CommandText = "UPDATE tickets SET typ_id='" + typId + "', ticket_datum = '" + ticketDate + "' WHERE fest_id='" + festId + "' AND gebr_id ='" + userId + "'";

                    cmd.ExecuteNonQuery();
                    tr.Commit();
                    success = true;
                }
                return success;
            }
            catch (Exception ex)
            {
                Logs.Log(ex.Message + " " + ex.StackTrace);
                return false;
            }
        }

        public bool deleteTickets(int festId, int userId)
        {
            bool success = false;
            try
            {
                if (conn.State == ConnectionState.Open)
                {
                    tr = conn.BeginTransaction();
                    cmd = new SqlCommand();
                    cmd.Transaction = tr;
                    cmd.CommandType = CommandType.Text;
                    cmd.Connection = conn;
                    cmd.CommandText = "DELETE FROM tickets WHERE fest_id='" + festId + "' AND gebr_id='" + userId + "'";

                    cmd.ExecuteNonQuery();
                    tr.Commit();
                    success = true;
                }
                else
                {
                    conn.Open();
                    tr = conn.BeginTransaction();
                    cmd = new SqlCommand();
                    cmd.Transaction = tr;
                    cmd.CommandType = CommandType.Text;
                    cmd.Connection = conn;
                    cmd.CommandText = "DELETE FROM tickets WHERE fest_id='" + festId + "' AND gebr_id='" + userId + "'";

                    cmd.ExecuteNonQuery();
                    tr.Commit();
                    success = true;
                }
                return success;
            }
            catch (Exception ex)
            {
                Logs.Log(ex.Message + " " + ex.StackTrace);
                return false;
            }
        }

        public bool updateTickettypes(int typId, String typDesc, Decimal typPrice)
        {
            bool success = false;
            try
            {
                if (conn.State == ConnectionState.Open)
                {
                    tr = conn.BeginTransaction();
                    cmd = new SqlCommand();
                    cmd.Transaction = tr;
                    cmd.CommandType = CommandType.Text;
                    cmd.Connection = conn;
                    cmd.CommandText = "UPDATE tickettypes SET typ_omschr='" + typDesc + "', typ_prijs='" + typPrice + "' WHERE typ_id='" + typId + "'";

                    cmd.ExecuteNonQuery();
                    tr.Commit();
                    success = true;
                }
                else
                {
                    conn.Open();
                    tr = conn.BeginTransaction();
                    cmd = new SqlCommand();
                    cmd.Transaction = tr;
                    cmd.CommandType = CommandType.Text;
                    cmd.Connection = conn;
                    cmd.CommandText = "UPDATE tickettypes SET typ_omschr='" + typDesc + "', typ_prijs='" + typPrice + "' WHERE typ_id='" + typId + "'";

                    cmd.ExecuteNonQuery();
                    tr.Commit();
                    success = true;
                }
                return success;
            }
            catch (Exception ex)
            {
                Logs.Log(ex.Message + " " + ex.StackTrace);
                return false;
            }
        }

        public bool deleteTickettypes(int typId)
        {
            bool success = false;
            try
            {
                if (conn.State == ConnectionState.Open)
                {
                    tr = conn.BeginTransaction();
                    cmd = new SqlCommand();
                    cmd.Transaction = tr;
                    cmd.CommandType = CommandType.Text;
                    cmd.Connection = conn;
                    cmd.CommandText = "DELETE FROM tickettypes WHERE typ_id='" + typId + "'";

                    cmd.ExecuteNonQuery();
                    tr.Commit();
                    success = true;
                }
                else
                {
                    conn.Open();
                    tr = conn.BeginTransaction();
                    cmd = new SqlCommand();
                    cmd.Transaction = tr;
                    cmd.CommandType = CommandType.Text;
                    cmd.Connection = conn;
                    cmd.CommandText = "DELETE FROM tickettypes WHERE typ_id='" + typId + "'";

                    cmd.ExecuteNonQuery();
                    tr.Commit();
                    success = true;
                }
                return success;
            }
            catch (Exception ex)
            {
                Logs.Log(ex.Message + " " + ex.StackTrace);
                return false;
            }
        }

        public bool updateTickettypesperfestival(int festId, int typId, int number)
        {
            bool success = false;
            try
            {
                if (conn.State == ConnectionState.Open)
                {
                    tr = conn.BeginTransaction();
                    cmd = new SqlCommand();
                    cmd.Transaction = tr;
                    cmd.CommandType = CommandType.Text;
                    cmd.Connection = conn;
                    cmd.CommandText = "UPDATE tickettypesperfestival SET aantal='" + number + "' WHERE fest_id='" + festId + "' AND typ_id ='" + typId + "'";

                    cmd.ExecuteNonQuery();
                    tr.Commit();
                    success = true;
                }
                else
                {
                    conn.Open();
                    tr = conn.BeginTransaction();
                    cmd = new SqlCommand();
                    cmd.Transaction = tr;
                    cmd.CommandType = CommandType.Text;
                    cmd.Connection = conn;
                    cmd.CommandText = "UPDATE tickettypesperfestival SET aantal='" + number + "' WHERE fest_id='" + festId + "' AND typ_id ='" + typId + "'";

                    cmd.ExecuteNonQuery();
                    tr.Commit();
                    success = true;
                }
                return success;
            }
            catch (Exception ex)
            {
                Logs.Log(ex.Message + " " + ex.StackTrace);
                return false;
            }
        }

        public bool deleteTickettypesperfestival(int festId, int typId)
        {
            bool success = false;
            try
            {
                if (conn.State == ConnectionState.Open)
                {
                    tr = conn.BeginTransaction();
                    cmd = new SqlCommand();
                    cmd.Transaction = tr;
                    cmd.CommandType = CommandType.Text;
                    cmd.Connection = conn;
                    cmd.CommandText = "DELETE FROM tickettypesperfestival WHERE fest_id='" + festId + "' AND typ_id='" + typId + "'";

                    cmd.ExecuteNonQuery();
                    tr.Commit();
                    success = true;
                }
                else
                {
                    conn.Open();
                    tr = conn.BeginTransaction();
                    cmd = new SqlCommand();
                    cmd.Transaction = tr;
                    cmd.CommandType = CommandType.Text;
                    cmd.Connection = conn;
                    cmd.CommandText = "DELETE FROM tickettypesperfestival WHERE fest_id='" + festId + "' AND typ_id='" + typId + "'";

                    cmd.ExecuteNonQuery();
                    tr.Commit();
                    success = true;
                }
                return success;
            }
            catch (Exception ex)
            {
                Logs.Log(ex.Message + " " + ex.StackTrace);
                return false;
            }
        }

        public bool updateRoles(Guid roleId, Guid ApplicationId, String roleName, String desc)
        {
            bool success = false;
            try
            {
                if (conn.State == ConnectionState.Open)
                {
                    tr = conn.BeginTransaction();
                    cmd = new SqlCommand();
                    cmd.Transaction = tr;
                    cmd.CommandType = CommandType.Text;
                    cmd.Connection = conn;
                    cmd.CommandText = "UPDATE Roles SET ApplicationId='" + ApplicationId + "', RoleName='" + roleName + "', Description='" + desc + "' WHERE RoleId='" + roleId + "'";

                    cmd.ExecuteNonQuery();
                    tr.Commit();
                    success = true;
                }
                else
                {
                    conn.Open();
                    tr = conn.BeginTransaction();
                    cmd = new SqlCommand();
                    cmd.Transaction = tr;
                    cmd.CommandType = CommandType.Text;
                    cmd.Connection = conn;
                    cmd.CommandText = "UPDATE Roles SET ApplicationId='" + ApplicationId + "', RoleName='" + roleName + "', Description='" + desc + "' WHERE RoleId='" + roleId + "'";

                    cmd.ExecuteNonQuery();
                    tr.Commit();
                    success = true;
                }
                return success;
            }
            catch (Exception ex)
            {
                Logs.Log(ex.Message + " " + ex.StackTrace);
                return false;
            }
        }

        public bool deleteRoles(Guid roleId)
        {
            bool success = false;
            try
            {
                if (conn.State == ConnectionState.Open)
                {
                    tr = conn.BeginTransaction();
                    cmd = new SqlCommand();
                    cmd.Transaction = tr;
                    cmd.CommandType = CommandType.Text;
                    cmd.Connection = conn;
                    cmd.CommandText = "DELETE FROM Roles WHERE RoleId='" + roleId + "'";

                    cmd.ExecuteNonQuery();
                    tr.Commit();
                    success = true;
                }
                else
                {
                    conn.Open();
                    tr = conn.BeginTransaction();
                    cmd = new SqlCommand();
                    cmd.Transaction = tr;
                    cmd.CommandType = CommandType.Text;
                    cmd.Connection = conn;
                    cmd.CommandText = "DELETE FROM Roles WHERE RoleId='" + roleId + "'";

                    cmd.ExecuteNonQuery();
                    tr.Commit();
                    success = true;
                }
                return success;
            }
            catch (Exception ex)
            {
                Logs.Log(ex.Message + " " + ex.StackTrace);
                return false;
            }
        }
    }
}